package zflx_new;

import org.apache.log4j.Logger;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Properties;

/**
 * @ Auther: o2o-rd-0008
 * @ Date:   2020/7/16 16:51
 * @ Param:  ${PARAM}
 * @ Description:
 */
public class g_Sqlutils_op {

    private static Logger logger = Logger.getLogger(g_tables.class);

    /**
     * 基础店铺表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_allplat_shop_BDL_insert(Connection conn,
                                                   String schem1, String name1,
                                                   String schem2, String name2,
                                                   String platformId,
                                                   String platformName,
                                                   String timeStamp) {
        PreparedStatement stmt = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            String[] platformArr = new String[]{"9", "20", "99"};

            StringBuilder sb = new StringBuilder();
            //1.0创建临时表
            sb.append("create temporary table temp_shop_" + platformName + " as \n");
            sb.append("select \n" +
                    "distinct t1.*, \n" +
                    "t2.uniqueid reg_id \n" +
                    "from \n" +
                    "(select  \n" +
                    "*, \n" +
                    "case  \n");
            if (Arrays.asList(platformArr).contains(platformId)) {
                sb.append("when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n");
                sb.append("when (city = '苏州市'  or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市') then district\n");
            } else {
                sb.append("when (province = '广东省' and (city = '中山市' or city = '东莞市')) then town \n");
                sb.append("when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市') then aedzid \n");
            }

            sb.append("else district end  districts \n" +
                    "from   " + schem2 + "." + name2 + " ) t1 \n" +
                    "left join \n" +
                    "(select distinct * from dbadmin.address_mapping_p_c_d_show_2018 where timeStamp =   '" + timeStamp + "'  ) t2 \n" +
                    "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district);\n");
            sb.append("\n");
            //2.0插入到基础店铺表
            sb.append("insert into " + schem1 + ".\"" + name1 + "\"\n");
            sb.append("(region_id,shopid,sellcount,salesamount,timestamp,platformid,userId,shopName)\n");
            sb.append("select \n" +
                    "distinct reg_id as reg_id,shopid,totalsellCount,totalsalesAmount,timeStamp,platformid,'-1' as userId,shopName \n" +
                    "from \n" +
                    "temp_shop_" + platformName + "  \n");

            if("3".equals(platformId) || "16".equals(platformId) || "13".equals(platformId) || "24".equals(platformId) || "23".equals(platformId)
                    || "20".equals(platformId) || "99".equals(platformId)){
                sb.append("where province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛') and reg_id is not null;\n");

            }else {
                sb.append("where province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛') and is_oversea != 'true' and reg_id is not null;\n");
            }

            sb.append("\n");
            //3.0更新基础店铺表region_id为null的数据,插入到基础店铺表
            sb.append("insert into " + schem1 + ".\"" + name1 + "\"\n");
            sb.append("(region_id,shopid,sellcount,salesamount,timestamp,platformid,userId,shopName)\n");
            sb.append("select\n");
            sb.append("distinct region_id as reg_id,shopid,totalsellCount,totalsalesAmount,timeStamp,platformid,'-1' as userId,shopName\n" +
                    "from(\n" +
                    "select distinct t1.*,t2.uniqueid region_id from  \n" +
                    "( \n" +
                    "select shopid,province, \n" +
                    "case when ( \n" +
                    "province like '北京市' or province like '上海市'  \n" +
                    "or province like '重庆市' or province like '天津市' ) then province else city end as city, \n" +
                    "cast('0' as text) as districts,totalsellcount,totalsalesamount,timestamp,platformid,shopname\n" +
                    "from   temp_shop_" + platformName + "   where reg_id is null ) t1  \n" +
                    "left join  \n" +
                    "(select distinct * from dbadmin.address_mapping_p_c_d_show_2018 where timeStamp =   '" + timeStamp + "'  ) t2 \n" +
                    "on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)\n" +
                    ");");


            //执行普通SQL语句。
            stmt = conn.prepareStatement(sb.toString());
            int i = stmt.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_allplat_shop_BDL_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }


    /**
     * 公司信息表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     */
    public static void o2o_allPlat_shop_BDL_company_insert(Connection conn,
                                                           String schem1, String name1,
                                                           String schem2, String name2,
                                                           String platformId,
                                                           String platformName,
                                                           String timeStamp
    ) {
        PreparedStatement pst = null;
        try {
            /**
             * 方法 executeQuery:用于产生单个结果集的语句，例如 SELECT 语句。
             * 方法 executeUpdate:用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
             * 方法 execute:用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。
             */
            pst = conn.prepareStatement(
                    "insert into \"" + schem1 + "\".\"" + name1 + "\"\n" +
                            "(timestamp,platformid,shopid,company_name,address,region_id)\n" +
                            "select distinct timestamp,platformid,shopid,name,address," +
                            "CASE  WHEN substr(regional_id,5,6)='00' and platformid='9' THEN substr(regional_id,0,4) else regional_id END region_id" +
                            " from \n" +
                            "\"" + schem2 + "\".\"" + name2 + "\"");
            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_allPlat_shop_BDL_company_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 畅销店铺
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_bestsell_shop_APP_insert(Connection conn,
                                                    String schem1, String name1,
                                                    String schem2, String name2,
                                                    String platformId,
                                                    String platformName,
                                                    String timeStamp) {
        //
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n");
            sb.append("(region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address)\n");
            sb.append("SELECT\n");
            sb.append("distinct region_id,shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address\n");
            sb.append("FROM\n");
            sb.append("(SELECT\n");
            sb.append("region_id,t1.shopid,sellcount,salesamount,timestamp,platformid,shopName,name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address, \n");
            sb.append("row_number() over(partition by region_id,province,city,district,town,aedzid order by salesamount desc) rank\n");
            sb.append("FROM  \n");
            sb.append("(SELECT  \n");
            sb.append("region_id,shopid,sellcount,salesamount,timestamp,platformid \n");
            sb.append("FROM \"" + schem2 + "\".\"" + name2 + "\" \n");
            if ("99".equals(platformId)) {
                sb.append("WHERE (platformid = '46' or \n" +
                        "platformid = '45' or \n" +
                        "platformid = '49' or \n" +
                        "platformid = '47' or \n" +
                        "platformid = '48' or \n" +
                        "platformid = '52' or \n" +
                        "platformid = '38' or \n" +
                        "platformid = '40' or \n" +
                        "platformid = '51' or \n" +
                        "platformid = '42' or \n" +
                        "platformid = '39' or \n" +
                        "platformid = '50' or \n" +
                        "platformid = '43' or \n" +
                        "platformid = '41') \n");
            } else {
                sb.append("WHERE platformid  ='" + platformId +"'");
            }

            sb.append(" AND timeStamp  ='" + timeStamp + "') t1\n");
            sb.append("left JOIN\n");
            sb.append("(select shopId,shopName,");
            if ("5".equals(platformId)) {
                sb.append("name,'' as creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address \n");
            } else if ("10".equals(platformId)) {
                sb.append("name,creditlevel,latitude,longitude,shopurl,province,city,district,town,aedzid,starts,address \n");
            } else if ("9".equals(platformId)) {
                sb.append("name,'' as creditlevel,cast('-1' as text) as latitude,cast('-1' as text) as longitude,shopurl,province,city,district,cast('-1' as text) as town,cast('-1' as text) as aedzid,cast('-1' as text) as starts,address \n");
            } else if ("99".equals(platformId)) {
                sb.append("name,'' as creditlevel,cast('-1' as text) as latitude,cast('-1' as text) as longitude,'' as shopurl,province,city,district,cast('-1' as text) as town,cast('-1' as text) as aedzid,cast('-1' as text) as starts,address \n");
            }else {
                sb.append("'' as name,'' as creditlevel,latitude,longitude,shopurl,province,city,district,'' as town,'' as aedzid,'' as starts,address \n");
            }
            sb.append("from temp_shop_" + platformName + ") t2 \n");
            sb.append("ON t1.shopId = t2.shopId) where rank <= 300");

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();
            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_bestsell_shop_APP_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }


    }


    /**
     * 人气店铺
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_sentiment_shop_APP_insert(Connection conn,
                                                     String schem1, String name1,
                                                     String schem2, String name2,
                                                     String platformId,
                                                     String platformName,
                                                     String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n");
            sb.append("(shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fans,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address)\n");
            sb.append("SELECT \n");
            sb.append("shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address\n");
            sb.append("FROM\n");
            sb.append("(SELECT\n");
            sb.append("shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address,\n");
            sb.append("row_number() over(partition by platformid,region_id,province,city,district,town,aedzId order by fan desc ) rank\n");
            sb.append("FROM\n");
            sb.append("(SELECT\n");
            sb.append("t1.shopid,region_id,sellcount,salesamount,timestamp,platformid,shopName,fan,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address\n");
            sb.append("FROM  \n");
            sb.append("(SELECT  \n");
            sb.append("region_id,shopid,sellcount,salesamount,timestamp,platformid\n");
            sb.append("FROM \"" + schem2 + "\".\"" + name2 + "\"\n");
            sb.append("WHERE platformid  ='" + platformId + "' AND timestamp  = '" + timeStamp + "') t1\n");
            sb.append("LEFT JOIN\n");
            if ("5".equals(platformId) || "10".equals(platformId)) {
                sb.append("(select shopId,shopName,cast(fans as int8) fan,name,latitude,longitude,shopurl,province,city,district,town,aedzId,starts,evaluates,address \n");
            } else if ("16".equals(platformId) || "13".equals(platformId) || "24".equals(platformId)) {
                sb.append("(select shopId,shopName,cast((case when shopCommentCount ='None' then '0' else shopCommentCount end) as int8) fan,'' as name,latitude,longitude,shopurl,province,city,district,'' as town,'' as aedzId,'' as starts,evaluates,address \n");
            } else if ("23".equals(platformId)) {
                sb.append("(select shopId,shopName,cast(shopCommentCount as int8) fan,'' as name,latitude,longitude,shopurl,province,city,district,'' as town,'' as aedzId,'' as starts,evaluates,address \n");
            } else if ("9".equals(platformId)) {
                sb.append("(select shopId,shopName,cast(fans as int8) fan,name,'' as latitude,'' as longitude,shopurl,province,city,district,'' as town,'' as aedzId,starts,evaluates,address ");
            }
            sb.append("from  temp_shop_" + platformName + ") t2\n");
            sb.append("ON t1.shopId = t2.shopId) t3) WHERE rank <=300");

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_sentiment_shop_APP_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 创建单品临时表
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_bestsell_product_APP_create(Connection conn,
                                                       String schem1, String name1,
                                                       String platformId,
                                                       String platformName,
                                                       String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();
            //1.0创建临时表
            sb.append("create temporary table temp_good_" + platformName + "_" + timeStamp + " as \n");
            sb.append("select\n");
            sb.append("distinct\n");

            if ("20".equals(platformId)) {
                sb.append("'false' as is_oversea,\n");
            } else {
                sb.append("t1.is_oversea,\n");
            }

            sb.append("t1.province,\n");
            sb.append("t1.city,\n");
            sb.append("t1.district,\n");

            if ("9".equals(platformId)) {
                sb.append("'' as origincountryname,\n");
                sb.append("t1.originproduction,\n");
            } else if ("10".equals(platformId) || "5".equals(platformId) || "3".equals(platformId)) {
                sb.append("t1.origincountryname,\n");
                sb.append("t1.originproduction,\n");
            } else if ("20".equals(platformId)) {
                sb.append("'' as origincountryname,\n");
                sb.append("'' as originproduction,\n");
            }

            sb.append("t1.good_id,\n");
            sb.append("t1.title,\n");

            if ("20".equals(platformId)) {
                sb.append("'' as goodurl,\n");
            } else {
                sb.append("t1.goodurl,\n");
            }


            sb.append("t1.images,\n");
            sb.append("t1.salesamount,\n");
            sb.append("t1.sellcount,\n");
            sb.append("t1.pricetext,\n");


            if ("5".equals(platformId)) {
                sb.append("t1.fans,\n");
                sb.append("'' as favcount,\n");
            } else if ("10".equals(platformId) || "9".equals(platformId)) {
                sb.append("t1.fans,\n");
                sb.append("t1.favcount,\n");
            } else if ("3".equals(platformId) || "20".equals(platformId)) {
                sb.append("'' as fans,\n");
                sb.append("'' as favcount,\n");
            }

            if ("20".equals(platformId)) {
                sb.append("'' as brandname_cn,\n");
                sb.append("'' as name,\n");
            } else {
                sb.append("t1.brandname_cn,\n");
                sb.append("t1.name,\n");
            }


            sb.append("t1.shopname,\n");

            if ("20".equals(platformId)) {
                sb.append("'' as shopurl,\n");
            } else {
                sb.append("t1.shopurl,\n");
            }

            sb.append("t1.platformid,\n");
            sb.append("'' as start_time,\n");
            sb.append("'' as end_time,\n");
            sb.append("t1.firstcategoryid,\n");
            sb.append("t1.secondcategoryid,\n");
            sb.append("t1.thirdcategoryid,\n");
            sb.append("t1.fourthcategoryid,\n");

            if ("20".equals(platformId)) {
                sb.append("'' as brandname_en,\n");
            } else {
                sb.append("t1.brandname_en,\n");
            }

            sb.append("t1.regional_id,\n");

            if ("9".equals(platformId) || "20".equals(platformId)) {
                sb.append("'' as town,\n");
                sb.append("'' as aedzid,\n");
            } else if ("10".equals(platformId) || "5".equals(platformId) || "3".equals(platformId)) {
                sb.append("t1.town,\n");
                sb.append("t1.aedzid,\n");
            }
            if ("9".equals(platformId) || "20".equals(platformId) || "3".equals(platformId)) {
                sb.append("'' as starts,\n");
            } else {
                sb.append("t1.starts,\n");
            }

            sb.append("t1.address,\n");
            sb.append("t1.evaluates,\n");
            sb.append("t1.timestamp,\n");


            if ("5".equals(platformId)) {
                sb.append("t1.shopid,\n");
            } else {
                sb.append("t1.shopid,\n");
                sb.append("t1.products,\n");
            }
            sb.append("t1.districts\n");
            sb.append("from\n");
            sb.append("(select \n");
            sb.append("*,\n");
            sb.append("case \n");
            sb.append("when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n");
            sb.append("when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟' or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n");
            sb.append("else district end  districts\n");
            sb.append("from \"" + schem1 + "\".\"" + name1 + "\") t1;\n");

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_bestsell_product_APP_create方法："+platformName + "平台---" + timeStamp + "---创建" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }

    }


    /**
     * 畅销农产品单品数据导入
     *
     * @param conn
     * @param scheme
     * @param name
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_bestsell_product_APP_insert(Connection conn,
                                                       String scheme, String name,
                                                       String platformId,
                                                       String platformName,
                                                       String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("INSERT INTO \"" + scheme + "\".\"" + name + "\"\n");
            sb.append("(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n");
            sb.append("select\n");
            sb.append("t4.good_id,t4.sellcounts,t4.sales,t4.platformId,t4.timestamp,t4.reg_id,t4.shopid,t4.shopname,t4.shopurl,t4.title,t4.goodurl,t4.name\n");
            sb.append("from\n");
            sb.append("(select\n");
            sb.append("*,row_number() over(partition by reg_id,province,city,district order by sales desc) rank\n");
            sb.append("from\n");
            sb.append("(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,cast(salesamount as float8) sales,platformId,province,city,district,\n");
            sb.append("timestamp,reg_id,shopid,shopname,shopurl,title,goodurl,name from \n");
            sb.append("(select t1.*, \n");
            if ("5".equals(platformId)) {
                sb.append("t3.products,\n");
            }
            sb.append("t2.uniqueid reg_id\n");
            sb.append("from temp_good_" + platformName + "_" + timeStamp + " t1\n");
            sb.append("left join\n");
            sb.append("(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timeStamp + "') t2\n");
            sb.append("on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)\n");

            if ("5".equals(platformId)) {
                sb.append("left join\n");
                sb.append("(SELECT thirdcategoryid,products FROM dbadmin.\"O2O_stand_products_IDL\" WHERE TIMESTAMP = '" + timeStamp + "' \n");
                sb.append("  AND flag_product_level = '2' \n");
                sb.append("GROUP BY\n");
                sb.append("  thirdcategoryid,products)  t3\n");
                sb.append("  on t1.thirdcategoryid=t3.thirdcategoryid\n");
            }

            sb.append("where good_id is not null and products ='1001' and is_oversea != 'true'\n");
            sb.append("and t1.province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛')))) t4\n");
            sb.append("where t4.rank <= 150\n");


            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_bestsell_product_APP_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 全网畅销单品
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_bestsell_APP_insert(Connection conn,
                                               String schem1, String name1,
                                               String schem2, String name2,
                                               String platformId,
                                               String platformName,
                                               String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();


            sb.append("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n");
            sb.append("(good_id,sellCount,salesAmount,platformid,timeStamp,region_id,shopid\n");
            sb.append(",shopname,shopurl,title,goodurl,name,province,city,district)\n");
            sb.append("select\n");
            sb.append("t1.good_id,t1.sellcount,t1.salesamount,t1.platformId\n");
            sb.append(",t1.timestamp,t1.regional_id,t1.shopid,t1.shopname,\n");
            if ("20".equals(platformId)) {
                sb.append("'' as shopurl,");
            } else {
                sb.append("t1.shopurl,");
            }

            sb.append("t1.title,\n");

            if ("9".equals(platformId)) {
                sb.append("t1.goodurl, \n");
                sb.append("'' as name, \n");
            } else if ("20".equals(platformId) || "16".equals(platformId) || "13".equals(platformId) || "24".equals(platformId) || "23".equals(platformId)) {
                sb.append("'' as goodurl, \n");
                sb.append("'' as name, \n");
            } else {
                sb.append("t1.goodurl, \n");
                sb.append("t1.name, \n");
            }


            sb.append("t1.province,t1.city,t1.district from\n");
            sb.append("(\n");
            sb.append("select *,row_number() over(partition by regional_id,province,city\n");
            sb.append(",district order by cast(salesamount as float8) desc) rank\n");
            sb.append("from\n");

            if(platformId.equals("16")| platformId.equals("13")| platformId.equals("24")
                    | platformId.equals("23")){
                sb.append("\"" + schem2 + "\".\"" + name2 + "\"\n");
            }else{
                sb.append(" temp_good_" + platformName + "_" + timeStamp + "\n");
            }

            sb.append("where \n");
            if ("9".equals(platformId) || "10".equals(platformId) || "5".equals(platformId) || "3".equals(platformId)) {
                sb.append("is_oversea != 'true' and \n");
            }
            sb.append("province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛')\n");
            sb.append(" ) t1\n");
            sb.append("where t1.rank <= 50\n");

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_bestsell_APP_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 人气农产品单品数据导入
     *
     * @param conn
     * @param schem1
     * @param name1
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_sentiment_product_APP_insert(Connection conn,
                                                        String schem1, String name1,
                                                        String platformId,
                                                        String platformName,
                                                        String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n");
            sb.append("(good_id,sellCount,favcount,salesAmount,platformid,timeStamp,region_id,shopid,shopname,shopurl,title,goodurl,name)\n");
            sb.append("SELECT\n");
            sb.append("t1.good_id,t1.sellcounts,t1.favs,t1.salesamount,t1.platformId,t1.timestamp,t1.reg_id,t1.shopid,t1.shopname,t1.shopurl,t1.title,t1.goodurl,t1.name\n");
            sb.append("FROM\n");
            sb.append("(SELECT\n");
            sb.append("*,row_number() over(partition by reg_id,province,city,district order by favs desc) rank\n");
            sb.append("FROM \n");
            sb.append("(SELECT good_id,cast(cast(sellCount as float8) as bigint) as sellcounts,salesamount,cast(favcount as int8) favs,platformId,province,city,district,\n");
            sb.append("timestamp,regional_id as reg_id,shopid,shopname,shopurl,title,goodurl, \n");

            if ("10".equals(platformId)) {
                sb.append("name\n");
            } else {
                sb.append("'' as name\n");
            }

            sb.append("from temp_good_" + platformName + "_" + timeStamp + "\n");

            sb.append("where good_id is not null and products ='1001'\n");
            sb.append("and province NOT IN ('0','海外','台湾省','香港特别行政区','澳门特别行政区','南海诸岛'))) t1\n");
            sb.append("where t1.rank <= 150\n");

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_sentiment_product_APP_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 价格段数据导入
     * @param conn
     * @param schem1
     * @param name1
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_products_price_APP_insert(Connection conn,
                                                        String schem1, String name1,
                                                        String platformId,
                                                        String platformName,
                                                        String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("insert into \"" + schem1 + "\".\"" + name1 + "\"\n");
            sb.append("select distinct platformid,province,city,district,firstcategoryid,secondcategoryid,thirdcategoryid,\n");
            sb.append("fourthcategoryid,sellcount,salesamount,flag_region_level,flag_cate_level,timestamp,sumPriceText,region_id,goodCount\n");
            sb.append(" from \n");
            sb.append("(");
            sb.append("select t3.platformid,t3.province,t3.city,t3.district,t3.firstcategoryid,t3.secondcategoryid,t3.thirdcategoryid,\n");
            sb.append("     t3.fourthcategoryid,t3.sellcount,t3.salesamount, '5' flag_region_level, '3' flag_cate_level,t3.timestamp,t3.sumPriceText,t3.goodCount,t4.uniqueid region_id\n");
            sb.append(" from \n");
            sb.append("(");
            sb.append("select t1.good_id,t1.platformid,t1.pricetext, t1.province,t1.city,t1.district,t1.firstcategoryid,\n");
            sb.append("          t1.secondcategoryid,t1.thirdcategoryid,t1.fourthcategoryid,t1.sellcount,t1.salesamount, '5' flag_region_level, '3' flag_cate_level,t1.timestamp,\n");
            sb.append("          sum(pricetext) over(partition by t1.province,t1.city,t1.district) sumPriceText, \n");
            sb.append("          count(good_id) over(partition by t1.province,t1.city,t1.district) goodCount \n");
            sb.append("from temp_good_" + platformName + "_" + timeStamp + " t1\n");
            sb.append("left join dbadmin.\"O2O_stand_products_IDL\" t2\n");
            sb.append("          on t1.thirdcategoryid = t2.thirdcategoryid and t1.platformid = t2.platformid and t1.timestamp = t2.timestamp \n");
            sb.append("          where t2.product_type = '1'");
            sb.append(") t3 \n");
            sb.append("left join \n");
            sb.append("test_liu.\"address_mapping_district\" t4\n");
            sb.append("on t3.province = t4.province and t3.city = t4.city and t3.district = t4.district");
            sb.append(")\n");
            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_products_price_APP_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 创建小平台跨境大屏临时表
     * @param conn
     * @param schem1
     * @param name1
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_screen_ovresea_app_create(Connection conn,
                                                     String schem1, String name1,
                                                     String platformId,
                                                     String platformName,
                                                     String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("create temporary table temp_good_" + platformName + "_"+timeStamp+" as\n");
            sb.append("select \n");
            sb.append("distinct \n");
            sb.append("t1.is_oversea, \n");
            sb.append("t1.province, \n");
            sb.append("t1.city, \n");
            sb.append("t1.district, \n");
            sb.append("t1.origincountryname, \n");
            if("kaola".equals(platformName) || "beibei".equals(platformName) || "jumei".equals(platformName)
                    || "dangdang".equals(platformName) || "guomei".equals(platformName) || "yunji".equals(platformName)){
                sb.append("'' as originproduction, \n");
            }else {
                sb.append("t1.originproduction, \n");
            }
            sb.append("t1.good_id, \n");
            sb.append("t1.title, \n");
            sb.append("t1.goodurl, \n");
            sb.append("t1.images, \n");
            sb.append("t1.salesamount, \n");
            sb.append("t1.sellcount, \n");
            if("beibei".equals(platformName) || "jumei".equals(platformName) || "yunji".equals(platformName)){
                sb.append("t1.fans, \n");
            }else{
                sb.append("'' as fans, \n");
            }
            if("dangdang".equals(platformName)){
                sb.append("t1.favcount, \n");
            }else {
                sb.append("'' as favcount, \n");
            }
            sb.append("t1.brandname_cn, \n");
            sb.append("t1.name, \n");
            sb.append("t1.shopname, \n");
            if("kaola".equals(platformName) || "beibei".equals(platformName) || "jumei".equals(platformName) || "yunji".equals(platformName)){
                sb.append("t1.shopurl, \n");
            }else{
                sb.append("'' as shopurl, \n");
            }
            sb.append("t1.platformid, \n");
            sb.append("'' as start_time, \n");
            sb.append("'' as end_time, \n");
            sb.append("t1.firstcategoryid, \n");
            sb.append("t1.secondcategoryid, \n");
            sb.append("t1.thirdcategoryid, \n");
            sb.append("t1.brandname_en, \n");
            sb.append("t1.regional_id, \n");
            sb.append("'' as town, \n");
            sb.append("'' as aedzid, \n");
            sb.append("'' as starts, \n");
            sb.append("t1.address, \n");
            if("beibei".equals(platformName) || "dangdang".equals(platformName)){
                sb.append("t1.evaluates, \n");
            }else{
                sb.append("'' as evaluates, \n");
            }
            sb.append("t1.timestamp, \n");
            sb.append("t1.shopid, \n");
            if("miya".equals(platformName)){
                sb.append("t1.products, \n");
            }else{
                sb.append("'' as products, \n");
            }
            sb.append("t2.uniqueid reg_id \n");
            sb.append("from \n");
            sb.append("(select  \n");
            sb.append("*, \n");
            sb.append("case  \n");
            sb.append("when (province = '广东省' and (city = '中山市' or city = '东莞市')) then district \n");
            sb.append("when (city = '苏州市' or  city = '湛江市' or city = '武汉市' or city = '威海市' or city = '锡林郭勒盟'  or city = '宿迁市' or city = '烟台市' or city = '东营市') then district\n");
            sb.append("else district end  districts\n");
            sb.append("from \"" + schem1 + "\".\"" + name1 + "\") t1\n");
            sb.append("left join\n");
            sb.append("(select province,city,district,uniqueid from  dbadmin.address_mapping_p_c_d_show_2018 where timestamp = '" + timeStamp + "') t2\n");
            sb.append("on concat(t1.province,t1.city,t1.districts) = concat(t2.province,t2.city,t2.district)");

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_screen_ovresea_app_create方法："+platformName + "平台---" + timeStamp + "---创建" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 跨境大屏表数据导入
     * @param conn
     * @param schem1
     * @param name1
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_screen_ovresea_app_insert(Connection conn,
                                                     String schem1, String name1,
                                                     String platformId,
                                                     String platformName,
                                                     String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("INSERT INTO \"" + schem1 + "\".\"" + name1 + "\"\n");
            sb.append("(\n");
            sb.append("province,\n");
            sb.append("city,\n");
            sb.append("district,\n");
            sb.append("originCountryName,\n");
            sb.append("originProduction,\n");
            sb.append("good_id,\n");
            sb.append("title,\n");
            sb.append("goodurl,\n");
            sb.append("images,\n");
            sb.append("salesAmount,\n");
            sb.append("sellcount,\n");
            sb.append("fans, \n");
            sb.append("favcount,\n");
            sb.append("brandname_cn,\n");
            sb.append("name,\n");
            sb.append("shopname,\n");
            sb.append("shopurl,\n");
            sb.append("platformid,\n");
            sb.append("timestamp,\n");
            sb.append("start_time,\n");
            sb.append("end_time,\n");
            sb.append("firstcategoryid,\n");
            sb.append("secondcategoryid,\n");
            sb.append("thirdcategoryid,\n");
            sb.append("brandname_en,\n");
            sb.append("regional_id,\n");
            sb.append("town,\n");
            sb.append("aedzid,\n");
            sb.append("starts,\n");
            sb.append("address,\n");
            sb.append("evaluates,\n");
            sb.append("shopid\n");
            sb.append(")\n");
            sb.append("select \n");
            sb.append("province ,\n");
            sb.append("city,\n");
            sb.append("district,\n");
            if("taobao".equals(platformName)){
                sb.append("'' as originCountryName,\n");
            }else{
                sb.append("originCountryName,\n");
            }
            sb.append("originProduction,\n");
            sb.append("good_id,\n");
            sb.append("title,\n");
            sb.append("goodurl,\n");
            if("pinduoduo".equals(platformName)){
                sb.append("'' as images,\n");
            }else {
                sb.append("images,\n");
            }
            sb.append("salesAmount,\n");
            sb.append("cast(sellcount as float8),\n");
            if("suning".equals(platformName) || "pinduoduo".equals(platformName)){
                sb.append("'' fans,\n");
                sb.append("'' favcount,\n");
            }else{
                sb.append("fans,\n");
                sb.append("favcount,\n");
            }
            if("pinduoduo".equals(platformName)){
                sb.append("'' as brandname_cn,\n");
            }else {
                sb.append("brandname_cn,\n");
            }
            sb.append("name,\n");
            sb.append("shopname,\n");
            sb.append("shopurl,\n");
            sb.append("platformid,\n");
            sb.append("timestamp,\n");
            sb.append("'' start_time,\n");
            sb.append("'' end_time,\n");
            sb.append("firstcategoryid,\n");
            sb.append("secondcategoryid,\n");
            sb.append("thirdcategoryid,\n");
            if("pinduoduo".equals(platformName)){
                sb.append("'' as brandname_en,\n");
            }else {
                sb.append("brandname_en,\n");
            }
            sb.append("regional_id,\n");
            if("suning".equals(platformName)){
                sb.append("town,\n");
                sb.append("aedzid,\n");
                sb.append("'' as starts,\n");
            }else if("tmall".equals(platformName) || "taobao".equals(platformName) || "jd".equals(platformName)){
                sb.append("'' as town,\n");
                sb.append("'' as aedzid,\n");
                sb.append("starts,\n");
            }else if("pinduoduo".equals(platformName)){
                sb.append("'' as town,\n");
                sb.append("'' as aedzid,\n");
                sb.append("'' as starts,\n");
            }else{
                sb.append("town,\n");
                sb.append("aedzid,\n");
                sb.append("starts,\n");
            }
            sb.append("address,\n");
            sb.append("evaluates,\n");
            sb.append("shopid\n");
            sb.append("from\n ");
            sb.append("temp_good_" + platformName + "_"+timeStamp+"\n");
            sb.append("where is_oversea = 'true'\n");

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_screen_ovresea_app_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 企业个人信息数据备份
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_company_or_person_create(Connection conn,
                                                     String schem1, String name1,
                                                     String schem2, String name2,
                                                     String platformId,
                                                     String platformName,
                                                     String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("--备份数据\n");
            sb.append("delete from " + schem1 + ".\"" + name1 + "\";\n");
            sb.append("insert into " + schem1 + ".\"" + name1 + "\"(\n");
            sb.append("shopid ,\n");
            sb.append("sellcount ,\n");
            sb.append("salesamount  ,\n");
            sb.append("\"timestamp\" ,\n");
            sb.append("platformid ,\n");
            sb.append("start_time ,\n");
            sb.append("end_time ,\n");
            sb.append("region_id ,\n");
            sb.append("is_liveshop ,\n");
            sb.append("userid ,\n");
            sb.append("shopname,\n");
            sb.append("company_or_personal\n");
            sb.append(")\n");
            sb.append("select\n");
            sb.append("shopid ,\n");
            sb.append("sellcount ,\n");
            sb.append("salesamount  ,\n");
            sb.append("\"timestamp\" ,\n");
            sb.append("platformid ,\n");
            sb.append("start_time ,\n");
            sb.append("end_time ,\n");
            sb.append("region_id ,\n");
            sb.append("is_liveshop ,\n");
            sb.append("userid ,\n");
            sb.append("shopname,\n");
            sb.append("company_or_personal\n");
            sb.append("from " + schem2 + ".\"" + name2 + "\"\n");
            sb.append("where timestamp='"+timeStamp+"';\n");
            sb.append("--删除数据\n");
            sb.append("delete from " + schem2 + ".\"" + name2 + "\" where timestamp='" + timeStamp + "';\n");


            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_allPlat_shop_BDL_company_or_person_create方法："+platformName + "平台---" + timeStamp + "---创建" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 企业个人信息数据更新导入
     * @param conn
     * @param schem1
     * @param name1
     * @param schem2
     * @param name2
     * @param schem3
     * @param name3
     * @param platformId
     * @param platformName
     * @param timeStamp
     */
    public static void o2o_allPlat_shop_BDL_company_or_person_insert(Connection conn,
                                                     String schem1, String name1,
                                                     String schem2, String name2,
                                                     String schem3, String name3,
                                                     String platformId,
                                                     String platformName,
                                                     String timeStamp) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("insert into " + schem2 + ".\"" + name2 + "\"(\n");
            sb.append("shopid ,\n");
            sb.append("sellcount ,\n");
            sb.append("salesamount  ,\n");
            sb.append("\"timestamp\" ,\n");
            sb.append("platformid ,\n");
            sb.append("start_time ,\n");
            sb.append("end_time ,\n");
            sb.append("region_id ,\n");
            sb.append("is_liveshop ,\n");
            sb.append("userid ,\n");
            sb.append("shopname,\n");
            sb.append("company_or_personal\n");
            sb.append(")\n");
            sb.append("select\n");
            sb.append("t1.shopid ,\n");
            sb.append("t1.sellcount ,\n");
            sb.append("t1.salesamount  ,\n");
            sb.append("t1.\"timestamp\" ,\n");
            sb.append("t1.platformid ,\n");
            sb.append("t1.start_time ,\n");
            sb.append("t1.end_time ,\n");
            sb.append("t1.region_id ,\n");
            sb.append("t1.is_liveshop ,\n");
            sb.append("t1.userid ,\n");
            sb.append("t1.shopname,\n");
            if("9".equals(platformId) || "5".equals(platformId) || "20".equals(platformId)){
                sb.append("    case\n");
                sb.append("\twhen t1.platformid = '"+platformId+"' and length(t2.company_name) in (6,9,12) \n");
                sb.append("\tand t2.company_name not in ('-1','0','1','01','测试','测试店铺','金徽商贸','金合商贸','蓝天商贸','金恒商贸','博宇商贸') \n");
                sb.append("\tand substr(t2.company_name,1,1) not in ('0','1','2','3','4','5','6','7','8','9') then '2'\n");
                sb.append("\twhen t1.platformid = '"+platformId+"' and length(t2.company_name)>12 \n");
                sb.append("\tand t2.company_name not in ('-1','0','1','01','测试','测试店铺') \n");
                sb.append("\tand substr(t2.company_name,1,1) not in ('0','1','2','3','4','5','6','7','8','9') then '1'\n");
                if("9".equals(platformId) || "20".equals(platformId)){
                    sb.append("else '2'\n");
                }else{
                    sb.append("else '1'\n");
                }
                sb.append("end as company_or_personal\n");
                sb.append("from " + schem1 + ".\"" + name1 + "\" t1\n");
                sb.append("left join\n");
                sb.append("(select * from " + schem3 + ".\"" + name3 + "\" where platformid='"+platformId+"' and timestamp='" + timeStamp + "') t2\n");
                sb.append("on t1.shopid=t2.shopid and t1.platformid=t2.platformid\n");
                sb.append("where t1.platformid='"+platformId+"' and t1.timestamp='" + timeStamp + "'");
            }else if("3".equals(platformId) || "10".equals(platformId)){
                sb.append("    '1'\n");
                sb.append("from " + schem2 + ".\"" + name2 + "\" t1 where platformid = '"+platformId+"' and timestamp='" + timeStamp + "'");
            }else if("16".equals(platformId) || "13".equals(platformId) || "24".equals(platformId) || "23".equals(platformId)){
                sb.append("    '0'\n");
                sb.append("from " + schem2 + ".\"" + name2 + "\" t1 where platformid = '"+platformId+"' and timestamp='" + timeStamp + "'");
            }else if("99".equals(platformId)){
                sb.append("    '0'\n");
                sb.append("from " + schem2 + ".\"" + name2 + "\" t1 where platformid not in ('9','10','5','3','20','16','13','23','24') and timestamp='" + timeStamp + "'");
            }

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("o2o_allPlat_shop_BDL_company_or_person_insert方法："+platformName + "平台---" + timeStamp + "---插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }


    /**
     * 数据导入工商企业信心表
     * @param conn
     * @param schem1
     * @param name1
     */
    public static void stand_company_insert(Connection conn,
                                              String schem1, String name1
    ) {
        PreparedStatement pst = null;
        try {
            StringBuilder sb = new StringBuilder();

            sb.append("insert into "+schem1+".\""+name1+"\" \n");
            sb.append("(key_id,company_name,crawl_date,boss_name,company_url,credit_code,email,licence_num,need_check,phone,registration_institution \n");
            sb.append(",tianyancha_company_name,approval_date,business_scope,business_status,company_loc,enterprise_type,industry \n");
            sb.append(",operating_period,organization_code,registered_capital,registered_capital_stard,registration_time,taxpayer_identification_number \n");
            sb.append(",start_time,end_time,company_name_back,timestamp) \n");
            sb.append("select \n");
            sb.append("a.key_id,a.companyname,a.crawl_date,a.boss_name,a.company_url,a.credit_code,a.email,a.licence_num,cast(a.need_check as boolean),a.phone,a.registration_institution \n");
            sb.append(",a.tianyancha_company_name,a.approval_date,a.business_scope,a.business_status,a.company_loc,a.enterprise_type,a.industry \n");
            sb.append(",a.operating_period,a.organization_code,a.registered_capital,a.registered_capital_stard,a.registration_time,a.taxpayer_identification_number \n");
            sb.append(",a.start_time,a.end_time,a.company_name_back,a.timestamp \n");
            sb.append("from \n");
            sb.append("( \n");
            sb.append("select distinct * from test_zsc.g_company \n");
            sb.append(") a \n");
            sb.append("left join \n");
            sb.append(""+schem1+".\""+name1+"\" b \n");
            sb.append("on a.key_id=b.key_id \n");
            sb.append("where b.key_id is null \n");

            pst = conn.prepareStatement(sb.toString());

            int i = pst.executeUpdate();

            logger.info("[Number of data insert:" + i + "]");
            System.out.println("stand_company_insert方法：插入" + i + "条数据");
            pst.close();
        } catch (SQLException e) {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

    /**
     * 更新跨境表
     * @param conn
     * @param schem1
     * @param name1
     */
    public static void is_oversea_plat_update(Connection conn,
                                              String schem1, String name1
    ) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(
                    "update \"" + schem1 + "\".\"" + name1 + "\"\n" +
                            "set \n" +
                            "\"regional_id\" = \n" +
                            "case \n" +
                            "when province = '北京市' and city = '市辖区' and district  ='东城区' then '110101'\n" +
                            "when province = '山东省' and city = '莱芜市' and district  ='0' then '3701'\n" +
                            "when province = '山东省' and city = '莱芜市' and district  ='莱城区' then '371202'\n" +
                            "when province = '北京市' and city = '朝阳区' and district  ='朝阳区' then '110105'\n" +
                            "when province = '北京市' and city = '海淀区' and district  ='海淀区' then '110108'\n" +
                            "when province = '山东省' and city = '莱芜市' and district  ='钢城区' then '371203'\n" +
                            "when province = '北京市' and city = '市辖区' and district  ='顺义区' then '110113'\n" +
                            "when province = '广东省' and city = '中山市' and district  ='中山市' then '4420'\n" +
                            "when province = '北京市' and city = '市辖区' and district  ='0' then '1101'\n" +
                            "when province = '北京市' and city = '大兴区' and district  ='大兴区' then '110115'\n" +
                            "when province = '广东省' and city = '东莞市' and district  ='东莞市' then '4419'\n" +
                            "when province = '湖北省' and city = '武汉市' and district  ='蔡甸区' then '420114'\n" +
                            "when province = '上海市' and city = '市辖区' and district  ='0' then '3101'\n" +
                            "when province = '吉林省' and city = '长春市' and district  ='0' then '2201'\n" +
                            "when province = '山东省' and city = '济南市' and district  ='0' then '3701'\n" +
                            "when province = '山东省' and city = '临沂市' and district  ='0' then '3713'\n" +
                            "when province = '江西省' and city = '南昌市' and district  ='0' then '3601'\n" +
                            "when province = '北京市' and city = '市辖区' and district  ='朝阳区' then '110105'\n" +
                            "when province = '山东省' and\tcity = '烟台市'\t   and district= '0' then '3706'\n" +
                            "when province = '广东省' and\tcity = '佛山市'\t   and district= '0' then '4406'\n" +
                            "when province = '上海市' and\tcity = '浦东新区'  and district= '浦东新区' then '310115'\n" +
                            "when province = '广东省' and\tcity = '东莞市'\t   and district= '0' then '4419'\n" +
                            "when province = '福建省' and\tcity = '福州市'\t   and district= '0' then '3501'\n" +
                            "when province = '广东省' and\tcity = '中山市'\t   and district= '0' then '4420'\n" +
                            "when province = '山东省' and\tcity = '济宁市'\t   and district= '0' then '3708'\n" +
                            "when province = '山西省' and\tcity = '太原市'\t   and district= '0' then '1401'\n" +
                            "when province = '北京市' and\tcity = '北京市'\t   and district= '0' then '1101'\n" +
                            "when province = '浙江省' and\tcity = '温州市'\t   and district= '0' then '3303'\n" +
                            "when province = '辽宁省' and\tcity = '沈阳市'\t   and district= '0' then '2101'\n" +
                            "when province = '四川省' and\tcity = '自贡市'\t   and district= '0' then '5103'\n" +
                            "when province = '广东省' and\tcity = '深圳市'\t   and district= '0' then '4403'\n" +
                            "when province = '浙江省' and\tcity = '宁波市'\t   and district= '0' then '3302'\n" +
                            "when province = '天津市' and\tcity = '天津市'\t   and district= '0' then '1201'\n" +
                            "when province = '上海市' and\tcity = '金山区'\t   and district= '金山区' then '310116'\n" +
                            "when province = '上海市' and\tcity = '上海市'\t   and district= '0' then '3101'\n" +
                            "when province = '江苏省' and\tcity = '宿迁市'\t   and district= '沭阳县' then '321322'\n" +
                            "when province = '安徽省' and\tcity = '合肥市'\t   and district= '0' then '3401'\n" +
                            "when province = '河南省' and\tcity = '开封市'\t   and district= '0' then '4102'\n" +
                            "when province = '浙江省' and\tcity = '杭州市'\t   and district= '0' then '3301'\n" +
                            "when province = '山东省' and\tcity = '青岛市'\t   and district= '0' then '3702'\n" +
                            "when province = '北京市' and\tcity = '北京市'\t   and district= '朝阳区' then '110105'\n" +
                            "when province = '江苏省' and\tcity = '苏州市'\t   and district= '姑苏区' then '320508'\n" +
                            "when province = '湖北省' and\tcity = '武汉市'\t   and district= '0' then '4201'\n" +
                            "when province = '四川省' and\tcity = '成都市'\t   and district= '0' then '5101'\n" +
                            "when province = '海外' and\tcity isnull\t   and district isnull then '99'\n" +
                            "when province = '台湾省' and\tcity = '0'\t   and district= '0' then '71'\n" +
                            "when province = '香港特别行政区' and\tcity isnull\t   and district isnull then '81'\n" +
                            "when province = '台湾省' and city isnull\t   and district isnull then '71'\n" +
                            "when province = '澳门特别行政区' and\tcity isnull\t   and district isnull then '82'\n" +
                            "when province = '0' and\tcity = '0'\t   and district= '0' then '-1'\n" +
                            "else \"regional_id\" end \n" +
                            "where regional_id is null");
            int i = pstmt.executeUpdate();
            logger.info("[Number of data update:" + i + "]");
            System.out.println("is_oversea_plat_update方法：更新" + i + "条数据");
            pstmt.close();
        } catch (SQLException e) {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }
}